package com.github.dengmin.mysql.service;

import com.github.dengmin.mysql.common.ConnectionPool;
import com.github.dengmin.mysql.common.SQL;
import com.github.dengmin.mysql.mapper.TableRowMapper;
import com.github.dengmin.mysql.model.DataBaseSchema;
import com.github.dengmin.mysql.model.DatabaseConnection;
import com.github.dengmin.mysql.model.DatabaseTable;
import com.github.dengmin.mysql.model.TableColumn;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author dengmin
 * @Created 2020/6/8 上午10:42
 */
@Service
public class DbMetaDataService {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private DatabaseConnectionService databaseConnectionService;

    public List<DataBaseSchema> getSchemas(int resourceId){
        DatabaseConnection connection = databaseConnectionService.getById(resourceId);
        DataSource dataSource = ConnectionPool.create(connection);
        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        List<Map<String,Object>> schemaMap = jdbc.queryForList (SQL.querySchema);
        List<DataBaseSchema> results = new ArrayList<>();
        schemaMap.forEach(x -> {
            String name = x.get("SCHEMA_NAME").toString();
            DataBaseSchema schema = new DataBaseSchema();
            schema.setText(name);
            schema.setType("db");
            initSchema(schema);
            results.add(schema);
        });
        return results;
    }

    public List<Map<String,Object>> getTableMenu(String dbName, int resourceId){
        List<Map<String,Object>> result = new ArrayList<>();
        DatabaseConnection connection = databaseConnectionService.getById(resourceId);
        DataSource dataSource = ConnectionPool.create(connection);
        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        List<Map<String,Object>> tableMap = jdbc.queryForList(SQL.queryTable, dbName);
        tableMap.forEach(x -> {
            Map<String, Object> table = new HashMap<>();
            table.put("text", x.get("TABLE_NAME").toString());
            table.put("icon", "iconfont icon-table");
            table.put("type", "table");
            table.put("db", dbName);
            result.add(table);
        });
        return result;
    }

    public List<DatabaseTable> getTables(String dbName, int resourceId){
        DatabaseConnection connection = databaseConnectionService.getById(resourceId);
        DataSource dataSource = ConnectionPool.create(connection);
        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        return jdbc.query(SQL.queryTable, new Object[]{dbName}, new TableRowMapper());
    }

    private void initSchema(DataBaseSchema schema){
        String[] tags = new String[]{"表", "视图"};
        for(int i = 0; i<tags.length; i++){
            Map<String,Object> children = new HashMap<>();
            children.put("text", tags[i]);
            children.put("icon", "iconfont icon-table");
            children.put("db", schema.getText());
            schema.getChildren().add(children);
        }
    }

    public List<TableColumn> queryTableColumns(String dbName, String table){
        return jdbcTemplate.execute(String.format(SQL.queryColumns, dbName, table), new PreparedStatementCallback<List<TableColumn>>() {
            @Override
            public List<TableColumn> doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
                List<TableColumn> tableColumns = new ArrayList<>();
                ResultSet resultSet = preparedStatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                TableColumn tableColumn = null;
                for(int i=1; i<columnCount+1; i++){
                    tableColumn = new TableColumn();
                    tableColumn.setName(metaData.getColumnName(i));
                    tableColumn.setDataType(metaData.getColumnTypeName(i));
                    tableColumn.setPrecision(metaData.getPrecision(i));
                    tableColumn.setAutoIncrement(metaData.isAutoIncrement(i));
                    tableColumn.setIsNullable(metaData.isNullable(i));
                    tableColumn.setReadonly(metaData.isReadOnly(i));
                    tableColumns.add(tableColumn);
                }
                return tableColumns;
            }
        });
    }

    public List<Map<String, Object>> getTableData(String dbName, String table, int pageNo, int pageSize){
        return jdbcTemplate.queryForList(String.format(SQL.queryCommon, dbName, table, (pageNo-1) * pageSize, pageSize));
    }


    public Integer getCount(String dbName, String table){
        return jdbcTemplate.execute(new StatementCallback<Integer>() {
            @Override
            public Integer doInStatement(Statement statement) throws SQLException, DataAccessException {
                ResultSet rs = statement.executeQuery(String.format(SQL.queryCount, dbName, table));
                rs.last();
                return rs.getRow();
            }
        });
    }

}
